package com.itheima.dao;

import com.github.pagehelper.Page;
import com.itheima.pojo.Member;
import org.apache.ibatis.annotations.*;

import java.util.List;
@Mapper//会员相关
public interface MemberDao {
    @Select("select * from t_member")
    List<Member> findAll();
    //根据条件查询
    @Select("<script> select * from t_member <if test=\"value != null and value.length > 0\"> where fileNumber = #{value} or phoneNumber = #{value} or name = #{value} </if> </script>")
    Page<Member> selectByCondition(String queryString);
    //新增会员
    @Insert("<selectKey resultType=\"java.lang.Integer\" order=\"AFTER\" keyProperty=\"id\">\n" +
            "            SELECT LAST_INSERT_ID()\n" +
            "        </selectKey>\n" +
            "        insert into t_member(fileNumber,name,sex,idCard,phoneNumber,regTime,password,email,birthday,remark)\n" +
            "        values (#{fileNumber},#{name},#{sex},#{idCard},#{phoneNumber},#{regTime},#{password},#{email},#{birthday},#{remark})")
    void add(Member member);
    //删除会员
    @Delete("delete from t_member where id = #{id}")
    void deleteById(Integer id);
    //根据id查询会员
    @Select("select * from t_member where id = #{id}")
    Member findById(Integer id);
    //根据电话查询会员
    @Select("select * from t_member where phoneNumber = #{phoneNumber}")
    Member findByTelephone(String telephone);
    //编辑会员
    @Update("<script> update t_member\n" +
            "        <set>\n" +
            "            <if test=\"fileNumber != null\">\n" +
            "                fileNumber = #{fileNumber},\n" +
            "            </if>\n" +
            "            <if test=\"name != null\">\n" +
            "                name = #{name},\n" +
            "            </if>\n" +
            "            <if test=\"sex != null\">\n" +
            "                sex = #{sex},\n" +
            "            </if>\n" +
            "            <if test=\"idCard != null\">\n" +
            "                idCard = #{idCard},\n" +
            "            </if>\n" +
            "            <if test=\"phoneNumber != null\">\n" +
            "                phoneNumber = #{phoneNumber},\n" +
            "            </if>\n" +
            "            <if test=\"regTime != null\">\n" +
            "                regTime = #{regTime},\n" +
            "            </if>\n" +
            "            <if test=\"password != null\">\n" +
            "                password = #{password},\n" +
            "            </if>\n" +
            "            <if test=\"email != null\">\n" +
            "                email = #{email},\n" +
            "            </if>\n" +
            "            <if test=\"birthday != null\">\n" +
            "                birthday = #{birthday},\n" +
            "            </if>\n" +
            "            <if test=\"remark != null\">\n" +
            "                remark = #{remark},\n" +
            "            </if>\n" +
            "        </set>\n" +
            "        where id = #{id} </script>")
    void edit(Member member);
    //根据日期统计会员数，统计指定日期之前的会员数，“<”和“>”号，不要用转义字符
    @Select("select count(id) from t_member where regTime &lt;= #{value}")
    Integer findMemberCountBeforeDate(String date);
    //根据日期统计会员数
    @Select("select count(id) from t_member where regTime = #{value}")
    Integer findMemberCountByDate(String date);
    //根据日期统计会员数，统计指定日期之后的会员数
    @Select("select count(id) from t_member where regTime &gt;= #{value}")
    Integer findMemberCountAfterDate(String date);
    //总会员数
    @Select("select count(id) from t_member")
    Integer findMemberTotalCount();
}
